package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.TreeMap;

import com.canin.model.Breed;
import com.canin.model.Food;
import com.canin.model.FoodFTN;
import com.canin.model.FoodProduct;
import com.canin.util.CaninUtil;

public class FoodDAO
{
	private Connection conn = null;

	public FoodDAO(Connection conn)
	{
		this.conn = conn;
	}
	
	public ArrayList<Food> getAllFood() throws Exception
	{
		ArrayList<Food> foodList = new ArrayList<Food>();

		String sql = "select * from food";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			Food food = new Food();
			food.setId( rs.getInt( "ID" ) );
			food.setFoodName( rs.getString( "food_name" ) );
			food.setFoodBrand( rs.getString( "food_brand" ) );
			food.setDescription( rs.getString( "description" ) );
			food.setDogBreedId( rs.getInt( "dog_breed_id" ) );
			food.setDogSize( rs.getString( "dog_size" ) );
			food.setDogAge( rs.getString( "dog_age" ) );
			food.setBeefIngredient( rs.getInt( "beef_ingredient" ) );
			food.setPorkIngredient( rs.getInt( "pork_ingredient" ) );
			food.setChickenIngredient( rs.getInt( "chicken_ingredient" ) );
			food.setFishIngredient( rs.getInt( "fish_ingredient" ) );
			food.setMilkIngredient( rs.getInt( "milk_ingredient" ) );
			food.setWheyIngredient( rs.getInt( "whey_ingredient" ) );
			food.setEggIngredient( rs.getInt( "egg_ingredient" ) );
			food.setSoybeanIngredient( rs.getInt( "soybean_ingredient" ) );
			food.setCornIngredient( rs.getInt( "corn_ingredient" ) );
			food.setKilocalorie( rs.getDouble( "kilocalorie" ) );
			food.setFoodType( rs.getString( "food_type" ) );
			food.setTaste( rs.getString( "taste" ) );
			food.setImageName( rs.getString( "imageName" ) );
			
			// Display
			food.setCurrencyPrice( CaninUtil.convertToConcurrency( (int)food.getPrice(), 2 ) );
			food.setCurrencyKilocalorie( CaninUtil.convertToConcurrency( (int)food.getKilocalorie(), 1 ) );
			
			foodList.add( food );
		}

		rs.close();
		ps.close();

		return foodList;
	}
	
	public ArrayList<Integer> getFoodFTNType( int foodId ) throws SQLException
	{
		ArrayList<Integer> foodFTNTypeList = new ArrayList<Integer>();
		
		String sql = "select distinct(FTN_Type_id) from food_target_nutrition " +
				"where id in ( select food_target_nutrition_id from food_ftn where food_id=? )";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, foodId);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			int FTNTypeId = rs.getInt( "FTN_Type_id" );
			foodFTNTypeList.add( FTNTypeId );
		}

		rs.close();
		ps.close();
		
		return foodFTNTypeList;
	}
	
	public ArrayList<FoodFTN> getFoodFTNList( int foodId ) throws SQLException
	{
		ArrayList<FoodFTN> foodFTNList = new ArrayList<FoodFTN>();
		
		String sql = "select FTN_Type_id, description from food_target_nutrition " +
				"where id in(select food_target_nutrition_id from food_ftn where food_id=?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, foodId);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			FoodFTN foodFTN = new FoodFTN();
			foodFTN.setTypeId( rs.getInt( "FTN_Type_id" ) );
			foodFTN.setFtnDescription( rs.getString( "description" ) );
			
			foodFTNList.add( foodFTN );
		}

		rs.close();
		ps.close();
		
		return foodFTNList;
	}
	
	public ArrayList<FoodProduct> getSizeAndPrice( int foodId ) throws SQLException
	{
		ArrayList<FoodProduct> foodProductList = new ArrayList<FoodProduct>();
		
		String sql = "select * from food_size where food_id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, foodId);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			FoodProduct foodProduct = new FoodProduct();
			foodProduct.setFoodId( rs.getInt( "food_id" ) );
			foodProduct.setSizeKg( rs.getDouble( "size_kg" ) );
			foodProduct.setPrice( rs.getInt( "price_baht" ) );

			foodProductList.add( foodProduct );
		}

		rs.close();
		ps.close();
		
		return foodProductList;
	}
	
	public ArrayList<FoodFTN> getFoodFTNAll() throws SQLException
	{
		ArrayList<FoodFTN> foodFTNList = new ArrayList<FoodFTN>();
		
		String sql = "SELECT distinct(f.id) as id, ft.description as type, f.target_nutrition as tn, f.description as dsc" +
				" FROM food_target_nutrition f, ftn_type ft" +
				" where f.FTN_Type_id=ft.id";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			FoodFTN foodFTN = new FoodFTN();
			foodFTN.setFtnId( rs.getInt( "id" ) );
			foodFTN.setTypeName( rs.getString( "type" ) );
			foodFTN.setTargetName( rs.getString( "tn" ) );
			foodFTN.setFtnDescription( rs.getString( "dsc" ) );
			
			foodFTNList.add( foodFTN );
		}

		rs.close();
		ps.close();
		
		return foodFTNList;
	}
	
	public ArrayList<FoodFTN> getFoodFTNTypeAll() throws SQLException
	{
		ArrayList<FoodFTN> foodFTNList = new ArrayList<FoodFTN>();
		
		String sql = "SELECT * FROM ftn_type";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			FoodFTN foodFTN = new FoodFTN();
			foodFTN.setTypeId( rs.getInt( "id" ) );
			foodFTN.setTypeName( rs.getString( "description" ) );
			
			foodFTNList.add( foodFTN );
		}

		rs.close();
		ps.close();
		
		return foodFTNList;
	}
	
	public void addFoodFtnType( FoodFTN foodFTN ) throws Exception
	{
		String sql = "insert into ftn_type(description) value(?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, foodFTN.getTypeName());

		ps.executeUpdate();
		ps.close();
	}
	
	public ArrayList<FoodFTN> getFoodFTNSubType( int typeId ) throws Exception
	{
		ArrayList<FoodFTN> foodFTNSubTypeList = new ArrayList<FoodFTN>();
		
		String sql = "SELECT * FROM food_target_nutrition where FTN_Type_id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, typeId);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			FoodFTN foodFTN = new FoodFTN();
			foodFTN.setFtnId( rs.getInt( "id" ) );
			foodFTN.setTypeId( rs.getInt( "FTN_Type_id" ) );
			foodFTN.setTargetName( rs.getString( "target_nutrition" ) );
			foodFTN.setFtnDescription( rs.getString( "description" ) );
			
			foodFTNSubTypeList.add( foodFTN );
		}

		rs.close();
		ps.close();
		
		return foodFTNSubTypeList;
	}
	
	public String getFoodFTNSubTypeName( int ftnId ) throws Exception
	{
		String foodFTNSubTypeName = "";
		
		String sql = "SELECT * FROM food_target_nutrition where id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, ftnId);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			foodFTNSubTypeName = rs.getString( "description" );
		}

		rs.close();
		ps.close();
		
		return foodFTNSubTypeName;
	}
	
	public void addFoodFtn( FoodFTN foodFTN ) throws Exception
	{
		String sql = "insert into food_target_nutrition(ftn_type_id, target_nutrition, description)" +
				" value(?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, foodFTN.getTypeId());
		ps.setString(2, foodFTN.getTargetName());
		ps.setString(3, foodFTN.getFtnDescription());

		ps.executeUpdate();
		ps.close();
	}
	
	public TreeMap<String, String> getAllFoodBrand() throws Exception
	{
		TreeMap<String, String> foodBrandList = new TreeMap<String, String>();
		
		String sql = "select distinct(food_brand) from food";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			foodBrandList.put( rs.getString( "food_brand" ), rs.getString( "food_brand" ) );
		}

		rs.close();
		ps.close();
		
		return foodBrandList;
	}
	
	public TreeMap<String, String> getAllFoodTaste() throws Exception
	{
		TreeMap<String, String> foodTasteList = new TreeMap<String, String>();
		
		String sql = "select distinct(taste) from food where taste!=\"\"";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			if ( rs.getString( "taste" ) != null )
			{
				foodTasteList.put( rs.getString( "taste" ), rs.getString( "taste" ) );
			}
		}

		rs.close();
		ps.close();
		
		return foodTasteList;
	}
	
	public TreeMap<Integer, String> getAllDogBreedId() throws Exception
	{
		TreeMap<Integer, String> foodDogBreedIdList = new TreeMap<Integer, String>();
		
		String sql = "select distinct(dog_breed_id) from food where dog_breed_id!=0";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		
		DAOFactory daoFactory = new DAOFactory();
		daoFactory.beginConnection();
		BreedDAO breedDAO = daoFactory.createBreedDAO();
		while (rs.next())
		{
			Breed breed = breedDAO.getBreedById(  rs.getInt( "dog_breed_id" ) );
			foodDogBreedIdList.put( rs.getInt( "dog_breed_id" ), breed.getBreedName() );
		}

		rs.close();
		ps.close();
		
		return foodDogBreedIdList;
	}
	
	public ArrayList<Food> getSelectedFood( Food selectedfood ) throws Exception
	{
		ArrayList<Food> selectedFoodList = new ArrayList<Food>();
		boolean criteria = false;
		
		String sql = "select * from food";
		if ( !"-1".equals( selectedfood.getFoodBrand() ) )
		{
			sql = sql + " where food_brand=\"" + selectedfood.getFoodBrand() + "\"";
			criteria = true;
		}
		
		if ( !"-1".equalsIgnoreCase( selectedfood.getFoodType() ) )
		{
			if ( criteria )
			{
				sql = sql + " and food_type=\"" + selectedfood.getFoodType() + "\"";
			}
			else
			{
				sql = sql + " where food_brand=\"" + selectedfood.getFoodType() + "\"";
			}
			criteria = true;
		}
		
		if ( selectedfood.getDogBreedId() != -1 )
		{
			if ( criteria )
			{
				sql = sql + " and dog_breed_id=" + selectedfood.getDogBreedId();
			}
			else
			{
				sql = sql + " where dog_breed_id=" + selectedfood.getDogBreedId();
			}
			criteria = true;
		}
		
		if ( !"-1".equalsIgnoreCase( selectedfood.getDogSize() ) )
		{
			if ( criteria )
			{
				sql = sql + " and dog_size=\"" + selectedfood.getDogSize() + "\"";
			}
			else
			{
				sql = sql + " where dog_size=\"" + selectedfood.getDogSize() + "\"";
			}
			criteria = true;
		}
		
		if ( !"-1".equalsIgnoreCase( selectedfood.getDogAge() ) )
		{
			if ( criteria )
			{
				sql = sql + " and dog_age=\"" + selectedfood.getDogAge() + "\"";
			}
			else
			{
				sql = sql + " where dog_age=\"" + selectedfood.getDogAge() + "\"";
			}
			criteria = true;
		}
		
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			Food food = new Food();
			food.setId( rs.getInt( "ID" ) );
			food.setFoodName( rs.getString( "food_name" ) );
			food.setFoodBrand( rs.getString( "food_brand" ) );
			food.setDescription( rs.getString( "description" ) );
			food.setDogBreedId( rs.getInt( "dog_breed_id" ) );
			food.setDogSize( rs.getString( "dog_size" ) );
			food.setDogAge( rs.getString( "dog_age" ) );
			food.setKilocalorie( rs.getDouble( "kilocalorie" ) );
			food.setFoodType( rs.getString( "food_type" ) );
			food.setTaste( rs.getString( "taste" ) );
			food.setImageName( rs.getString( "imageName" ) );
			
			selectedFoodList.add( food );
		}

		rs.close();
		ps.close();

		return selectedFoodList;
	}
	
	public void addNewFood1( Food food ) throws Exception
	{
		String sql = "insert into food(food_name, food_brand, description, dog_size, dog_age, food_type, taste, dog_breed_id)"
					+ " value(?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, food.getFoodName());
		ps.setString(2, food.getFoodBrand());
		ps.setString(3, food.getDescription());
		ps.setString(4, food.getDogSize());
		ps.setString(5, food.getDogAge());
		ps.setString(6, food.getFoodType());
		ps.setString(7, food.getTaste());
		ps.setInt(8, food.getDogBreedId());

		ps.executeUpdate();
		ps.close();
	}
	
	public int getLastFoodInserted() throws SQLException
	{
		int id = 0;
		
		String sql = "select max(id) from food;";
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		
		while (rs.next())
		{
			id = rs.getInt( "max(id)" );
		}

		rs.close();
		ps.close();
		
		return id;
	}
	
	public void updateIngredient( Food food ) throws SQLException
	{
		String sql ="UPDATE FOOD SET ingredient=?, beef_ingredient=?, pork_ingredient=?" +
					", chicken_ingredient=?, fish_ingredient=?" +
					", milk_ingredient=?, whey_ingredient=?, egg_ingredient=?" +
					", soybean_ingredient=?, corn_ingredient=?" +
					" WHERE id=?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, food.getIngredient());
		ps.setInt(2, food.getBeefIngredient());
		ps.setInt(3, food.getPorkIngredient());
		ps.setInt(4, food.getChickenIngredient());
		ps.setInt(5, food.getFishIngredient());
		ps.setInt(6, food.getMilkIngredient());
		ps.setInt(7, food.getWheyIngredient());
		ps.setInt(8, food.getEggIngredient());
		ps.setInt(9, food.getSoybeanIngredient());
		ps.setInt(10, food.getCornIngredient());
		ps.setInt(11, food.getId());

		ps.executeUpdate();
		ps.close();
	}
	
	public void addFTN( int foodId, int ftnId ) throws SQLException
	{
		String sql = "insert into food_ftn(food_id, food_target_nutrition_id) value(?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, foodId);
		ps.setInt(2, ftnId);

		ps.executeUpdate();
		ps.close();
	}
	
	public void addSizeAndPrice( int id, Double size, int price, int delivery ) throws SQLException
	{
		String sql = "insert into food_size(food_id, size_kg, price_baht, deliveryPrice) value(?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ps.setDouble(2, size);
		ps.setInt(3, price);
		ps.setInt(4, delivery);

		ps.executeUpdate();
		ps.close();
	}
	
	public void updateCallories( int id, double callories ) throws SQLException
	{
		String sql ="UPDATE FOOD SET kilocalorie=? WHERE id=?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setDouble(1, callories);
		ps.setInt(2, id);

		ps.executeUpdate();
		ps.close();
	}
	
	
	
	
}